﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using WeifenLuo.WinFormsUI.Docking;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace GgCarControl
{
    public partial class CLTCTJ_Form : DockContent
    {
        public static Database db =
          new OracleDatabase("Data Source=DG;User ID=dgrfid;Password=dgrfid;Unicode=True;Load Balance Timeout=1000");
        public CLTCTJ_Form()
        {
            InitializeComponent();
        }
        DataTable dt = new DataTable();
        private void button1_Click(object sender, EventArgs e)
        {
            string strbegin = dateTimePicker1.Value.ToString("yyyy-MM-dd")+" 00:00:00";
            string strend = dateTimePicker2.Value.ToString("yyyy-MM-dd") + "23:59:59";
            string strsjbegin = this.comboBox1.Text.ToString();
            string strsjend = this.comboBox2.Text.ToString();
            string strtcc = this.comboBox3.Text.ToString();
            string strlx = this.comboBox4.Text.ToString();
            string strcs = this.textBox1.Text.ToString();

            string strwhere1 = " LOGINTIME< TO_DATE('" + strbegin + "','yyyy-mm-dd hh24:mi:ss') ";
            string strwhere2 = " LOGINTIME>=TO_DATE('" + strbegin + "','yyyy-mm-dd hh24:mi:ss') AND LOGINTIME<=TO_DATE('" + strend + "','yyyy-mm-dd hh24:mi:ss') ";

            if (!strsjbegin.Equals("0") && !strsjend.Equals("0"))
            {
                strwhere2 += " AND TO_NUMBER(TO_CHAR(LOGINTIME,'hh24'))>=" + strsjbegin + " AND TO_NUMBER(TO_CHAR(LOGINTIME,'hh24'))<" + strsjend;
            }
            if (!strtcc.Equals("全部"))
            {
                strwhere1 += " AND CARDADD ='" + strtcc + "' ";
                strwhere2 += " AND CARDADD ='" + strtcc + "' ";
            }
            if (!strlx.Equals("全部"))
            {
                if (strlx.Equals("市入"))
                {
                    strwhere1 += " AND IOYARDID ='1' ";
                    strwhere2 += " AND IOYARDID ='1' ";
                }
                if (strlx.Equals("市提"))
                {
                    strwhere1 += " AND IOYARDID ='0' ";
                    strwhere2 += " AND IOYARDID ='0' ";
                }
            }
            try
            {
                strcs = int.Parse(strcs).ToString();
            }
            catch
            {
                strcs="0";
            }
            StringBuilder sbsql = new StringBuilder();
            sbsql.Append(" SELECT  CARGONAME,COUNT(*) DJS, ");
            sbsql.Append(" COUNT (CASE WHEN TO_NUMBER(TRUCKSTATUS) >0  THEN 1 ELSE NULL END) FCS, ");
            sbsql.Append(" (SELECT COUNT(*) FROM TRUCK_CARGO_LOGIN A WHERE TO_NUMBER(TRUCKSTATUS) <1 AND LOGINTIME< TO_DATE('" + strend + "','yyyy-mm-dd hh24:mi:ss')  AND A.CARGONAME=B.CARGONAME ) SYS, ");
          //  sbsql.Append(" COUNT (CASE WHEN TO_NUMBER(TRUCKSTATUS) >1  THEN 1 ELSE NULL END) SYS, ");
            sbsql.AppendFormat("(SELECT COUNT(*) FROM TRUCK_CARGO_LOGIN C WHERE C.CARGONAME=B.CARGONAME AND  TO_NUMBER(TRUCKSTATUS) <2 AND CEIL((SYSDATE -LOGINTIME) * 24) >={0}) CSS,", strcs);
          //  sbsql.AppendFormat(" COUNT (CASE WHEN TO_NUMBER(TRUCKSTATUS) <2 AND CEIL((SYSDATE -LOGINTIME) * 24) >={0} THEN 1 ELSE NULL END) CSS,  ", strcs);
            sbsql.AppendFormat(" COUNT(*)+(SELECT COUNT(*) FROM TRUCK_CARGO_LOGIN D WHERE   D.CARGONAME = B.CARGONAME AND TO_NUMBER(TRUCKSTATUS) <2 AND {0} ) ZCZS  ", strwhere1);
            sbsql.AppendFormat(" FROM TRUCK_CARGO_LOGIN B WHERE {0} GROUP BY CARGONAME ", strwhere2);


            sbsql.Append("  UNION ALL ");

            sbsql.Append(" SELECT '合计'CARGONAME ,sum(DJS) DJS,sum(FCS) FCS,sum(SYS) SYS,sum(CSS) CSS,sum(ZCZS) ZCZS   FROM (  ");

            sbsql.Append(" SELECT  CARGONAME,COUNT(*) DJS, ");
            sbsql.Append(" COUNT (CASE WHEN TO_NUMBER(TRUCKSTATUS) >0  THEN 1 ELSE NULL END) FCS, ");
            sbsql.Append(" (SELECT COUNT(*) FROM TRUCK_CARGO_LOGIN A WHERE TO_NUMBER(TRUCKSTATUS) <1 AND LOGINTIME< TO_DATE('" + strend + "','yyyy-mm-dd hh24:mi:ss')  AND A.CARGONAME=B.CARGONAME ) SYS, ");
            //sbsql.Append(" COUNT (CASE WHEN TO_NUMBER(TRUCKSTATUS) >1  THEN 1 ELSE NULL END) SYS, ");
            sbsql.AppendFormat("(SELECT COUNT(*) FROM TRUCK_CARGO_LOGIN C WHERE  C.CARGONAME=B.CARGONAME AND TO_NUMBER(TRUCKSTATUS) <2 AND CEIL((SYSDATE -LOGINTIME) * 24) >={0}) CSS,", strcs);
         
            // sbsql.AppendFormat(" COUNT (CASE WHEN TO_NUMBER(TRUCKSTATUS) <2 AND CEIL((SYSDATE -LOGINTIME) * 24) >={0} THEN 1 ELSE NULL END) CSS,  ", strcs);
            sbsql.AppendFormat(" COUNT(*)+(SELECT COUNT(*) FROM TRUCK_CARGO_LOGIN D WHERE  D.CARGONAME = B.CARGONAME AND TO_NUMBER(TRUCKSTATUS) <2 AND {0} ) ZCZS  ", strwhere1);
            sbsql.AppendFormat(" FROM TRUCK_CARGO_LOGIN B WHERE {0} GROUP BY CARGONAME ", strwhere2);

            sbsql.Append(" ) ");

            this.dataGridView1.AutoGenerateColumns = false;
            dt=db.ExecuteDataSet(db.GetSqlStringCommand(sbsql.ToString())).Tables[0];
            this.dataGridView1.DataSource = dt;

        }
        public bool ExportDataGridview(DataGridView dgv, bool isShowExcle)
        {
            if (dgv.Rows.Count == 0)
                return false;
            //建立Excel对象
            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Application.Workbooks.Add(true);
            excel.Visible = isShowExcle;
            //生成字段名称
            for (int i = 0; i < dgv.ColumnCount; i++)
            {
                excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
            }
            //填充数据
            for (int i = 0; i < dgv.RowCount - 1; i++)
            {
                for (int j = 0; j < dgv.ColumnCount; j++)
                {
                    if (dgv[j, i].ValueType == typeof(string))
                    {
                        excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
                    }
                    else
                    {
                        excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
                    }
                }
            }
            return true;
        }

        public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
        {



            if (tmpDataTable == null)

                return;

            int rowNum = tmpDataTable.Rows.Count;

            int columnNum = tmpDataTable.Columns.Count;

            int rowIndex = 1;

            int columnIndex = 0;



            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();// new ApplicationClass();

            xlApp.DefaultFilePath = "";

            xlApp.DisplayAlerts = true;

            xlApp.SheetsInNewWorkbook = 1;

            Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);



            //将DataTable的列名导入Excel表第一行

            foreach (DataColumn dc in tmpDataTable.Columns)
            {

                columnIndex++;

                xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;

            }



            //将DataTable中的数据导入Excel中

            for (int i = 0; i < rowNum; i++)
            {

                rowIndex++;

                columnIndex = 0;

                for (int j = 0; j < columnNum; j++)
                {

                    columnIndex++;

                    xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();

                }

            }

            //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));

            xlBook.SaveCopyAs(strFileName);

        }


        private void button2_Click(object sender, EventArgs e)
        {
            ExportDataGridview(dataGridView1, true);
            //SaveFileDialog saveFileDialog1 = new SaveFileDialog();
            ////设置文件类型 
            //saveFileDialog1.FileName = "车辆停车统计表.XLX"; //this.View.ThislistView.SelectedItems[0].SubItems[0].Text.ToString() + fileaddress.Substring(fileaddress.LastIndexOf("."));//fileaddress.Substring(fileaddress.LastIndexOf("/") + 1);
            //saveFileDialog1.Filter = " txt files(*.txt)|*.txt|All files(*.*)|*.*";
            ////设置默认文件类型显示顺序 
            //saveFileDialog1.FilterIndex = 2;
            ////保存对话框是否记忆上次打开的目录 
            //saveFileDialog1.RestoreDirectory = true;
            ////点了保s存按钮进入
            //string _fileName = "";
            //if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            //{
            //    //获得文件路径  
            //    string sss = saveFileDialog1.FileName.ToString();
            //    string strfileaddress = sss.Substring(0, sss.LastIndexOf('\\'));
            //    //  MessageBox.Show(sss.Substring(0,sss.LastIndexOf('\\')));


            //    DataTabletoExcel(dt, strfileaddress + "\\车辆停车统计表.XLS");
                
            //    // dtname = listdtname[ii];
            //    //  Download(localFilePath.Substring(localFilePath.LastIndexOf("\\") + 2), FilePath);
            //}
        }

        private void CLTCTJ_Form_Load(object sender, EventArgs e)
        {
            comboBox1.SelectedIndex = 0;
            comboBox2.SelectedIndex = 0;
            comboBox3.SelectedIndex = 0;
            comboBox4.SelectedIndex = 0;
           
        }
    }
}
